﻿CREATE PROCEDURE [dbo].[Sys_MessageManager_NewMessageToUsers]
	@messageContent nvarchar(500),
	@creatorUserID int,
	@revUserIDList varchar(500)
AS

	DECLARE @OutputTbl TABLE (ID INT)
	declare @messageID int

	set @messageID=-1;

	begin try
	   Begin  Tran 

		INSERT INTO sys_UserMessage([MessageContent], [CreatorUserID], [CreateDt],[IsDeleted])
		OUTPUT INSERTED.MessageID INTO @OutputTbl(ID)
		VALUES (@messageContent, @creatorUserID, GETDATE(),0)

		select @messageID=id from @OutputTbl

		if @messageID>0
		begin

			insert into sys_MessageUserMapping (UserID,MessageID,[IsRead],[ReadDt])
			select  value,@messageID,0,null from Split(@revUserIDList,',') where (not (value is null)) and value <> '' and  value <>@creatorUserID 

			commit tran
		end
		else
		begin
			rollback tran
		end

	end try
	begin catch
		rollback tran
	end catch

RETURN @messageID